package com.kuhh.dao.impl;

import com.kuhh.dao.IScoresDao;
import com.kuhh.pojo.ProfessionCourse;
import com.kuhh.pojo.Scores;
import com.kuhh.pojo.Students;
import com.kuhh.utils.JdbcUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ScoresDaoImpl implements IScoresDao {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @Override
    public List<ProfessionCourse> getCoursesByProfessionNo(String professionNo) {
        List<ProfessionCourse> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_profession_course where profession_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,professionNo);
            rs = ps.executeQuery();
            while(rs.next()){
                ProfessionCourse professionCourse = new ProfessionCourse();
                professionCourse.setCourseNo(rs.getString(1));
                professionCourse.setCourseName(rs.getString(2));
                professionCourse.setCourseType(rs.getString(3));
                professionCourse.setCourseCredit(rs.getString(4));
                professionCourse.setCourseNum(rs.getInt(5));
                professionCourse.setGradeNo(rs.getString(6));
                professionCourse.setProfessionNo(rs.getString(7));
                professionCourse.setTeacherNo(rs.getString(8));
                professionCourse.setCreateTime(rs.getTime(9));
                professionCourse.setUpdateTime(rs.getTime(10));
                list.add(professionCourse);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Students> getStudentsByProfessionNo(String professionNo) {
        List<Students> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from tb_students  where profession_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,professionNo);
            rs = ps.executeQuery();
            while(rs.next()){
                Students student = new Students();
                student.setStudentNo(rs.getString(1));
                student.setStudentName(rs.getString(2));
                student.setGradeNo(rs.getString(3));
                student.setCollegeNo(rs.getString(4));
                student.setProfessionNo(rs.getString(5));
                student.setClassNo(rs.getString(6));
                student.setDescription(rs.getString(7));
                student.setIdCard(rs.getString(8));
                student.setAge(rs.getInt(9));
                student.setGender(rs.getByte(10));
                student.setYear(rs.getString(11));
                student.setCreateTime(rs.getDate(12));
                student.setUpdateTime(rs.getDate(13));
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Scores> getScoreListByCourseNo(String courseNo) {
        List<Scores> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where course_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                list.add(scores);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Scores> getScoreListByStudentNo(String studentNo) {
        List<Scores> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where student_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentNo);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                list.add(scores);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Scores> getScoreListByTeacherNo(String teacherNo) {
        List<Scores> list = new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where course_no in(select course_no from tb_profession_course where teacher_no = ?) order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,teacherNo);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                list.add(scores);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public Scores getScoreByCourseNoAndStudentNo(String courseNo, String studentNo) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where course_no = ? and student_no = ? order by create_time desc";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setString(2,studentNo);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                return scores;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return null;
    }

    @Override
    public List<Scores> getTopScoreList(Integer top) {
        List<Scores> list =new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores order by score desc limit ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,top);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                list.add(scores);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public List<Scores> getCourseTopScoreList(String courseNo, Integer top) {
        List<Scores> list =new ArrayList<>();
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where course_no = ? order by score desc limit ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setInt(2,top);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                list.add(scores);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public Scores getScoreById(Integer id) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,course_no,student_no,score,grade_point,create_time,update_time from tb_scores where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while(rs.next()){
                Scores scores = new Scores();
                scores.setId(rs.getInt(1));
                scores.setCourseNo(rs.getString(2));
                scores.setStudentNo(rs.getString(3));
                scores.setScore(rs.getFloat(4));
                scores.setGradePoint(rs.getInt(5));
                scores.setCreateTime(rs.getTime(6));
                scores.setUpdateTime(rs.getTime(7));
                return scores;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return null;
    }

    @Override
    public boolean updateScore(Scores scores) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "update tb_scores set course_no = ?,student_no = ?,score = ?,grade_point = ?,update_time = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,scores.getCourseNo());
            ps.setString(2,scores.getStudentNo());
            ps.setFloat(3,scores.getScore());
            ps.setInt(4,scores.getGradePoint());
            ps.setDate(5, new Date(scores.getUpdateTime().getTime()));
            ps.setInt(6,scores.getId());
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean addScore(Scores scores) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into tb_scores(course_no,student_no,score,grade_point,create_time,update_time) values(?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1,scores.getCourseNo());
            ps.setString(2,scores.getStudentNo());
            ps.setFloat(3,scores.getScore());
            ps.setInt(4,scores.getGradePoint());
            ps.setDate(5, new Date(scores.getCreateTime().getTime()));
            ps.setDate(6, new Date(scores.getUpdateTime().getTime()));
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public boolean deleteScore(Integer id) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "delete from tb_scores where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return false;
    }

    @Override
    public int getBeforeScoreNum(String courseNo, float score) {
        try {
            conn = JdbcUtils.getConnection();
            String sql = "select count(*) from tb_scores where course_no = ? and score > ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setFloat(2,score);
            rs = ps.executeQuery();
            while (rs.next()){
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.close(conn,ps,rs);
        }
        return 0;
    }
}
